import codecs
dicts="""sbxh
ysjbh
lsslqfq
lsslqfh
hjsd"""
eles="""最大力(Fm)
抗拉强度(Rm)
上屈服强度(ReH)
下屈服强度(ReL)
断面收缩率(Z)
断裂总延伸率(At)
屈服点延伸率(Ae)
最大力塑性延伸率(Ag)
最大力总延伸率(Agt)
屈强比
断后伸长率(A)
弹性模量(E)
断后伸长率(A4D)
规定塑性延伸强度(Rp0.2)"""
args="""d0
a0
b0
D0
L0
du1
du2
FeH
FeL"""
# t
# sylx
# sygg
# temp1
# temp2
# temp3
# Fm1
# Fp02
# note"""
dicts=dicts.split("\n")
args=args.split("\n")
def gen_biaoqian():
  s1=["""JSON_VALUE(DICT_JSON,'$.%s') as %s""" %(i,i) for i in dicts]
  s1=",".join(s1)
  s1=s1+","
  print(s1)
  s2=["""JSON_VALUE(ftr.HI,'$.%s') as %s""" %(i,i) for i in args]
  s2=",".join(s2)
  s2=s2+","
  print(s2)
  out="""
  --通用基础信息
  -- 名称  英文名称
  -- 仪器编号  EQUIP_CODE
  -- 实验人 USER_NAME
  -- 实验人图片 USER_NAME_FILE
  -- 实验日期  USER_DATE
  -- 审核人 AUDIT_USER_NAME
  -- 审核人图片 AUDIT_USER_NAME_FILE
  -- 审核日期  AUDIT_USER_DATE
  -- 方法编号  METHOD_CODE
  SELECT
      ID,
      FLOW_TASK_ID,
      USER_ID,
      USER_NAME,
      USER_DATE,
      DESCRIPTION,
      REQ_ID,
      REQ_CODE,
      METHOD_CODE,
      METHOD_ID,
      STATUS,
      AUDIT_USER_ID,
      AUDIT_USER_NAME,
      AUDIT_USER_DATE,
      LIMS_ID,
      REASON,
      FTR_IDS,
      DOC_ID,
      EQUIP_CODE,
      DICT_JSON,
     %s
    (SELECT USER_NAME_FILE FROM [HANSON-HAP].dbo.[SCT_USER] WHERE ID = SIGN_USER_ID) as USER_NAME_FILE,
    (SELECT USER_NAME_FILE FROM [HANSON-HAP].dbo.[SCT_USER] WHERE ID = AUDIT_USER_ID) as AUDIT_USER_NAME_FILE
    FROM
      AUTOMATE_MESSAGE_QUEUE
    WHERE
      FLOW_TASK_ID = ${flowTaskId}
  --通用项目信息
  -- 名称  英文名称
  -- 仪器读数  HK
  -- 样品原号  SAMPLE_CODE
  -- 样品名称  SAMPLE_NAME
  -- 测定值 TEST_VALUES
  -- 编号  code
  -- 项目  ITEM_NAME
  select * from(SELECT
    ftr.REQ_CODE,
    ftr.REQ_ID,
    ftr.BAR_CODE,
    ftr.BAR_CODE+ISNULL('_'+ftr.SMALL_CODE,'') as code,
    ftr.SMALL_CODE,
    ftr.TEST_SAMPLE_ID,
    ftr.TEST_ITEM_ID,
    ftr.ITEM_ID,
    s.SAMPLE_CODE,
    s.METHOD_CODES,
    s.SAMPLE_NAME,
    ftr.HI as HI,
    ftr.TEST_VALUES,
    ftr.TEST_VALUES as TEST_VALUES1,
    ftr.HK,
    i.ITEM_NAME,
  	%s
  	right(ftr.BAR_CODE,CHARINDEX('-', REVERSE(ftr.BAR_CODE))-1) as num,
  left(ftr.BAR_CODE,CHARINDEX('-', ftr.BAR_CODE)-1) as pre
    FROM
      FTR_TEST_RESULT_HANDLE ftr
      LEFT JOIN TEST_SAMPLE s ON ftr.TEST_SAMPLE_ID = s.ID
      left join TEST_ITEM i on ftr.TEST_ITEM_ID = i.ID
    WHERE
      ftr.ID IN (${ftrIds}))temp
      order by pre,cast(num as int),SMALL_CODE""" % (s1,s2)
  f=codecs.open("gbiaoqian.sql","w",encoding="utf-8")
  f.write(out)
  f.close()
if __name__=="__main__":
  gen_biaoqian() 